"""
用户书架查询类
使用原生SQL优化查询性能，专门处理书架相关的复杂查询
"""

import aiomysql
from typing import List, Dict, Any, Optional
from db import DatabaseConnection, db_connection


class UserBookshelfQuery:
    """用户书架查询类，使用原生SQL优化性能"""

    def __init__(self):
        self.db_connection: DatabaseConnection = db_connection

    async def query_user_bookshelf_with_pagination(
        self,
        user_id: int,
        page: int = 1,
        limit: int = 10,
    ) -> tuple[int, List[Dict[str, Any]]]:
        """
        分页查询指定用户的书柜图书信息，最新收藏的书籍显示在前面

        Args:
            user_id: 用户ID
            page: 页码，从1开始
            limit: 每页数量

        Returns:
            tuple[int, List[Dict[str, Any]]]: 返回总数和书籍列表
            - int: 总书籍数量
            - List[Dict[str, Any]]: 书籍列表，每个字典包含完整图书信息及收藏时间，按添加时间倒序排列
        """
        offset = (page - 1) * limit

        # 查询书籍详细信息的SQL，按收藏时间倒序排列
        sql = """
        SELECT 
            b.*,
            ub.add_at,
            GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
            GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
        FROM user_bookshelf ub
        INNER JOIN books b ON ub.book_id = b.book_id
        LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
        LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
        WHERE ub.user_id = %s
        GROUP BY b.book_id, ub.add_at
        ORDER BY ub.add_at DESC
        LIMIT %s OFFSET %s
        """

        # 查询总数的SQL
        count_sql = """
        SELECT COUNT(*) as total
        FROM user_bookshelf
        WHERE user_id = %s
        """

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 先查询总数
                    await cursor.execute(count_sql, (user_id,))
                    count_result = await cursor.fetchone()
                    total = count_result["total"] if count_result else 0

                    # 查询书籍数据
                    await cursor.execute(sql, (user_id, limit, offset))
                    books = await cursor.fetchall()

                    # 处理分类数据
                    for book in books:
                        if book.get("main_categories"):
                            book["main_categories"] = book["main_categories"].split(",")
                        else:
                            book["main_categories"] = []

                        if book.get("sub_categories"):
                            book["sub_categories"] = book["sub_categories"].split(",")
                        else:
                            book["sub_categories"] = []

                    return total, books if books else []

        except Exception as e:
            print(f"查询用户书架失败: {e}")
            return 0, []

    async def search_user_bookshelf(
        self,
        user_id: int,
        keyword: str,
        main_category: Optional[str] = None,
        sub_category: Optional[str] = None,
        min_rating: float = 0,
        max_rating: float = 10,
        min_rating_count: int = 0,
        max_rating_count: Optional[int] = None,
        min_reading_count: int = 0,
        max_reading_count: Optional[int] = None,
        min_price: float = -1,
        max_price: Optional[float] = None,
        sort_by: str = "add_time",
        sort_order: str = "desc",
        page: int = 1,
        limit: int = 10,
    ) -> tuple[int, List[Dict[str, Any]]]:
        """
        在用户书架中综合搜索图书，支持关键字搜索和高级筛选
        与书城搜索保持一致，但限定范围为用户书架

        Args:
            user_id: 用户ID
            keyword: 搜索关键字，在标题、作者、简介中搜索
            main_category: 主分类
            sub_category: 子分类
            min_rating: 最低评分
            max_rating: 最高评分
            min_rating_count: 最低评分人数
            max_rating_count: 最高评分人数
            min_reading_count: 最低在读人数
            max_reading_count: 最高在读人数
            min_price: 最低价格，-1
            max_price: 最高价格
            sort_by: 排序字段，支持rating、rating_count、reading_count、price、publish_time、title、add_time
            sort_order: 排序顺序，支持asc（升序）和desc（降序）
            page: 页码，从1开始
            limit: 每页数量

        Returns:
            tuple[int, List[Dict[str, Any]]]: 返回总数和图书列表
        """
        # 处理分页参数
        offset = (page - 1) * limit

        # 构建SQL查询条件
        where_conditions = ["ub.user_id = %s"]
        params = [user_id]

        # 关键字搜索条件
        keyword_conditions = []
        if keyword and keyword.strip():
            search_keyword = f"%{keyword.strip()}%"

            # 在多个字段中搜索关键字
            search_fields = ["title", "author", "intro"]
            for field in search_fields:
                keyword_conditions.append(f"b.{field} LIKE %s")
                params.append(search_keyword)

            if keyword_conditions:
                where_conditions.append(f"({' OR '.join(keyword_conditions)})")

        # 分类筛选
        if main_category:
            where_conditions.append(
                "EXISTS (SELECT 1 FROM book_main_categories mc WHERE mc.book_id = b.book_id AND mc.main_category = %s)"
            )
            params.append(main_category)

        if sub_category:
            where_conditions.append(
                "EXISTS (SELECT 1 FROM book_sub_categories sc WHERE sc.book_id = b.book_id AND sc.sub_category = %s)"
            )
            params.append(sub_category)

        # 评分筛选
        if min_rating > 0:
            where_conditions.append("b.rating >= %s")
            params.append(min_rating)

        if max_rating is not None:
            where_conditions.append("b.rating <= %s")
            params.append(max_rating)

        # 评分人数筛选
        if min_rating_count > 0:
            where_conditions.append("b.rating_count >= %s")
            params.append(min_rating_count)

        if max_rating_count is not None:
            where_conditions.append("b.rating_count <= %s")
            params.append(max_rating_count)

        # 阅读量筛选
        if min_reading_count > 0:
            where_conditions.append("b.reading_count >= %s")
            params.append(min_reading_count)

        if max_reading_count is not None:
            where_conditions.append("b.reading_count <= %s")
            params.append(max_reading_count)

        # 价格筛选
        if min_price > -1:
            where_conditions.append("b.price >= %s")
            params.append(min_price)

        if max_price is not None:
            where_conditions.append("b.price <= %s")
            params.append(max_price)

        # 构建WHERE子句
        where_clause = " AND ".join(where_conditions)

        # 构建ORDER BY子句
        valid_sort_fields = {
            "rating": "b.rating",
            "rating_count": "b.rating_count",
            "reading_count": "b.reading_count",
            "price": "b.price",
            "publish_time": "b.publish_time",
            "title": "b.title",
            "add_time": "ub.add_at",
        }
        sort_field = valid_sort_fields.get(sort_by, "ub.add_at")
        sort_direction = "DESC" if sort_order.lower() == "desc" else "ASC"

        # 如果是关键字搜索，添加相关性评分
        relevance_score = ""
        relevance_params = []
        order_by_clause = ""

        if keyword and keyword.strip():
            search_keyword = f"%{keyword.strip()}%"
            relevance_score = """
            , CASE 
                WHEN b.title LIKE %s THEN 3
                WHEN b.author LIKE %s THEN 2
                ELSE 1
            END as relevance_score
            """
            relevance_params = [search_keyword, search_keyword]
            order_by_clause = f"ORDER BY relevance_score DESC, {sort_field} {sort_direction}, ub.add_at DESC"
        else:
            order_by_clause = f"ORDER BY {sort_field} {sort_direction}, ub.add_at DESC"

        # 查询书架中图书详细信息的SQL
        sql = f"""
        SELECT 
            b.*,
            ub.add_at{relevance_score},
            GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
            GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
        FROM user_bookshelf ub
        INNER JOIN books b ON ub.book_id = b.book_id
        LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
        LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
        WHERE {where_clause}
        GROUP BY b.book_id, ub.add_at
        {order_by_clause}
        LIMIT %s OFFSET %s
        """

        # 查询总数的SQL
        count_sql = f"""
        SELECT COUNT(DISTINCT b.book_id) as total
        FROM user_bookshelf ub
        INNER JOIN books b ON ub.book_id = b.book_id
        LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
        LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
        WHERE {where_clause}
        """

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 构建完整的参数列表
                    count_params = params.copy()
                    full_params = relevance_params + params + [limit, offset]

                    # 先查询总数
                    await cursor.execute(count_sql, count_params)
                    count_result = await cursor.fetchone()
                    total = count_result["total"] if count_result else 0

                    # 查询图书数据
                    await cursor.execute(sql, full_params)
                    books = await cursor.fetchall()

                    # 处理分类数据
                    for book in books:
                        if book.get("main_categories"):
                            book["main_categories"] = book["main_categories"].split(",")
                        else:
                            book["main_categories"] = []

                        if book.get("sub_categories"):
                            book["sub_categories"] = book["sub_categories"].split(",")
                        else:
                            book["sub_categories"] = []

                        # 移除relevance_score字段（内部使用）
                        book.pop("relevance_score", None)

                    return total, books if books else []

        except Exception as e:
            print(f"搜索用户书架失败: {e}")
            return 0, []
